# -*- coding: utf-8 -*-

import re
import sqlite3 as sqlite
import logging

import sumc


l = logging.getLogger("sumc.data")
l.addHandler(sumc._NullHandler())

tables = []

#XXX: add indexes!!!

tables.append('''
CREATE TABLE line (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    remote_id INTEGER NOT NULL,    /* NOT UNIQUE but unique with type */
    type INTEGER NOT NULL,         /* sumc.TRANSPORT_TYPES */
    label VARCHAR NOT NULL,        /* option text on SelectByLine.aspx */
    route_f INTEGER NOT NULL,      /* first direction id */
    route_f_label TEXT,            /* first direction label */
    route_b INTEGER NOT NULL,      /* second direction id */
    route_b_label TEXT,            /* second direction label */
    UNIQUE (remote_id, type),
    UNIQUE (label, type)
)''')

tables.append('''
CREATE TABLE stop (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    /* remote_id INTEGER NOT NULL,       oops, stops have multiple remote ids */
    code INTEGER UNIQUE NOT NULL,        /* keep only one record per stop code */
    name VARCHAR NOT NULL,               /* label with the code stripped */
    label VARCHAR NOT NULL               /* stop label as seen on SelectByLine.aspx */
)''')

tables.append('''
CREATE TABLE line_stop (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    line_id INTEGER NOT NULL REFERENCES line (id),
    route INTEGER NOT NULL,          /* either route_f or route_b from the line record*/
    stop_code INTEGER NOT NULL REFERENCES stop (code),
    stop_remote_id INTEGER NOT NULL, /* the id of this line+route+stop from SelectByLine.aspx */
    ordered INTEGER NOT NULL,        /* used to order the stops in a route*/
    UNIQUE (line_id, route, stop_code)
)''')


class DBHelper(object):
    
    def __init__(self, dbFile):
        self.conn = None
        self.dbFile = dbFile

    def createDatabase(self):
        self.conn = conn = sqlite.connect(self.dbFile)
        cur = conn.cursor()
        for table in tables:
            cur.execute(table)
        cur.close();
        conn.commit()
        
    def open(self):
        if not self.conn:
            self.conn = sqlite.connect(self.dbFile)
        
    def close(self):
        self.conn.close()
        self.conn = None
        
    def addLine(self, remoteId, ttype, label, routeF, routeB):
        l.info("addLine: %s %s %s %s", remoteId, label, routeF[0], routeB[0])
        cur = self.conn.cursor()
        cur.execute("INSERT INTO line ("
                    "  remote_id" \
                    ", type" \
                    ", label" \
                    ", route_f" \
                    ", route_f_label" \
                    ", route_b" \
                    ", route_b_label" \
                    ") VALUES (?,?,?,?,?,?,?)", 
                    (remoteId, ttype, label, 
                     routeF[0], routeF[1],
                     routeB[0], routeB[1]))
        
        id = cur.lastrowid
        cur.close()
        self.conn.commit()
        
        return id
    
    def addStop(self, label):
        m = sumc.re_stop.match(label)
        if (not m):
            l.warn("addStop: wrong label format: %s", label)
            return None
        
        code = m.group(1)
        name = m.group(2)
        
        l.info("addStop: %s %s", code, name)
        cur = self.conn.cursor()
        cur.execute("INSERT INTO stop (code, name, label)" \
                    " VALUES (?,?,?)", (code, name, label))
        id = cur.lastrowid
        cur.close()
        self.conn.commit()
        
        return id
    
    def addLineStopLink(self, lineId, stopId, route, remoteStopId):
        l.info("addLineStopLink: %s %s %s %s", lineId, stopId, route, remoteStopId)

        c = self.conn.execute("SELECT MAX(ordered) FROM line_stop" \
                      " WHERE line_id = ? AND route = ?",
                        (lineId, route))
        res = c.fetchone()
        c.close()
        ordered = res[0] and res[0] or 1
            
        cur = self.conn.cursor()
        cur.execute("INSERT INTO line_stop (" \
                    "  line_id" \
                    ", route" \
                    ", stop_code" \
                    ", stop_remote_id" \
                    ", ordered" \
                    ") VALUES (?,?,?,?,?)",
                    (lineId, route, stopId, remoteStopId, ordered))
        
        id = cur.lastrowid
        cur.close()
        self.conn.commit()
        return id
    
    def findStopByCode(self, stopCode):
        cur = self.conn.cursor()
        cur.execute("SELECT _id FROM stop WHERE code = ?", (stopCode, ))
        res = cur.fetchone()
        cur.close()
        if (res):
            return res[0]
        return None
    
if __name__ == '__main__':
    pass